package cho11436_service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import cho11436_Model.SungjukVO;
import cho11436__interfaces.SungJukService;

public class JDBCSungJuk implements SungJukService {

	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";

	// SQL문 작성
	private static final String insertSQL = " INSERT into SUNGJUK (SJNO,SNAME,KOR,ENG,MATH) VALUES (SJNO.nextval,?,?,?,?)";
	private static final String selectSQL = " select * from sungjuk order by sjno desc ";
	private static final String updateSQL = " update from sungjuk set name=? kor=? eng=? math=? ";
	private static final String deleteSQL = " delete from sungjuk where sjno = ? ";

	@Override
	public boolean newSungJuk(SungjukVO sj) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
			// ? 매개변수에 들어갈 실제 값을 지정
			ps.setString(1, sj.getName());
			ps.setInt(2, sj.getKor());
			ps.setInt(3, sj.getEng());
			ps.setInt(4, sj.getMat());

			int cnt = ps.executeUpdate();
			// 성공적으로 insert 되면 result를 true로
			if (cnt > 0)
				result = true;

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception ex) {

				}

		}

		return result;
	}

	@Override
	public String readSungjuk() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			// selectSql 실행 결과를 모두 출력
			while (rs.next()) {
				for (int i = 1; i <= 9; ++i)
					result += rs.getString(i);
				result += "\n";
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception ex) {

				}

		}
		return result;
	}

	@Override
	public boolean modifySungjuk(SungjukVO sj) {

		return true;
	}

	@Override
	public boolean removeSungjuk(String key) {

		return true;
	}

}
